package cs5226.dao;

import java.sql.SQLException;
import java.util.List;

import cs5226.model.PercentageSingleValueStatistics;
import cs5226.model.SingleValueStatistics;

public class RollbackSegmentDAO extends AWRStatisticsDAO {

	public static List<PercentageSingleValueStatistics> getRollbackPercentage() throws SQLException {
		String exprStat = "(100* sum(decode(a.stat_name,'user rollbacks', a.value)) / " +
				" (sum(decode(a.stat_name,'user rollbacks', a.value)) + " +
				" sum(decode(a.stat_name,'user commits', a.value))))";
		String awrTable = "DBA_HIST_SYSSTAT";
		
		return searchAWRStatistics(PercentageSingleValueStatistics.class, exprStat, awrTable, DAYSLATEST);
	}

	public static List<PercentageSingleValueStatistics> getRollbackPercentage(String fromDate, String toDate)
			throws SQLException {
		String exprStat = "(100* sum(decode(a.stat_name,'user rollbacks', a.value)) / " +
				" (sum(decode(a.stat_name,'user rollbacks', a.value)) + " +
				" sum(decode(a.stat_name,'user commits', a.value))))";
		String awrTable = "DBA_HIST_SYSSTAT";
		String filter = null;
		
		return searchAWRStatisticsWithinAPeriod(fromDate, toDate, PercentageSingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
	
	public static List<PercentageSingleValueStatistics> getRollbackPercentageLatest()
			throws SQLException {
		String exprStat = "(100* sum(decode(a.stat_name,'user rollbacks', a.value)) / " +
				" (sum(decode(a.stat_name,'user rollbacks', a.value)) + " +
				" sum(decode(a.stat_name,'user commits', a.value))))";
		String awrTable = "DBA_HIST_SYSSTAT";
		String filter = null;
		
		return searchAWRStatisticsLatest(PercentageSingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
	
	public static List<SingleValueStatistics> getRollbackPerSecond() throws SQLException {
		String exprStat = "(sum(decode(a.stat_name,'user rollbacks', a.value)) / " +
				" (extract(minute from (b.end_interval_time - b.begin_interval_time ))*60 + " +
				" extract(second from (b.end_interval_time - b.begin_interval_time ))))";
		String awrTable = "DBA_HIST_SYSSTAT";
		
		return searchAWRStatistics(SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST);
	}
	
	public static List<SingleValueStatistics> getRollbackPerSecond(String fromDate, String toDate)
			throws SQLException {
		String exprStat = "(sum(decode(a.stat_name,'user rollbacks', a.value)) / " +
				" (extract(minute from (b.end_interval_time - b.begin_interval_time ))*60 + " +
				" extract(second from (b.end_interval_time - b.begin_interval_time ))))";
		String awrTable = "DBA_HIST_SYSSTAT";
		String filter = null;
		
		return searchAWRStatisticsWithinAPeriod(fromDate, toDate, SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
	
	public static List<SingleValueStatistics> getRollbackPerSecondLatest()
			throws SQLException {
		String exprStat = "(sum(decode(a.stat_name,'user rollbacks', a.value)) / " +
				" (extract(minute from (b.end_interval_time - b.begin_interval_time ))*60 + " +
				" extract(second from (b.end_interval_time - b.begin_interval_time ))))";
		String awrTable = "DBA_HIST_SYSSTAT";
		String filter = null;
		
		return searchAWRStatisticsLatest(SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}

}
